| Oracle中如何导出存储过程、函数、包和触发器的定义语句？如何导出表的结构？如何导出索引的创建语句？ | 您所在的位置：网站首页 › dbeaver oracle 包体 › Oracle中如何导出存储过程、函数、包和触发器的定义语句？如何导出表的结构？如何导出索引的创建语句？ |
Oracle中如何导出存储过程、函数、包和触发器的定义语句？如何导出表的结构？如何导出索引的创建语句？
|
使用如下的脚本即可导出某个用户下的存储过程代码到/tmp/a.sql文件中：
SET PAGESIZE 0SET TRIMSPOOL ONSET LINESIZE 10000SET LONG 90000SET FEEDBACK OFFSET FEED OFF;SET ECHO OFFspool /tmp/a.sqlSELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)||CHR(10)||'/' FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'PROCEDURE'; spool OFF 打开文件后，简单处理一下即可。总体来说有两种方式来获取，第一，利用系统包DBMS_METADATA包中的GET_DDL函数来获取，第二，利用exp或expdp来获取。 下面来看第一种方式，如何利用系统包DBMS_METADATA包中的GET_DDL函数来获取对象的定义语句。下面是该函数的入参和出参： SQL> DESC DBMS_METADATA.GET_DDL PARAMETER TYPE MODE DEFAULT? ----------- -------- ---- -------- (RESULT) CLOB OBJECT_TYPE VARCHAR2 IN NAME VARCHAR2 IN SCHEMA VARCHAR2 IN Y VERSION VARCHAR2 IN Y MODEL VARCHAR2 IN Y TRANSFORM VARCHAR2 IN Y 其详细参数如下： l NAME 对象名称 l VERSION 对象原数据的版本 l TRANSFORM 默认值为DDL l 查看创建表SQL语句： SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U; n 查看创建主键的SQL语句： SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL; n 查看创建视图（VIEW）的SQL语句： SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'VIEW'; SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME'); n 查看创建触发器（TRIGGER）的SQL语句： SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'TRIGGER'; n 查看创建包（PACKAGE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'PACKAGE'; n 查看创建同义词（SYNONYM）的SQL语句： SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'SYNONYM'; n 查看创建角色（ROLE）的SQL语句： SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U; n 得到某个SCHEDULER JOB的创建语句： SELECT DBMS_METADATA.GET_DDL('PROCOBJ', D.JOB_NAME, D.OWNER) FROM DBA_SCHEDULER_JOBS D WHERE D.JOB_TYPE = 'STORED_PROCEDURE' AND D.STATE = 'SCHEDULED' AND D.SCHEDULE_NAME IS NULL; n lcnt++ if ( lcnt >= 30 ) { ln=substr( $0,s+1,length( substr($0,s+1))-1) t=index( ln, ")," ) if ( t==0 ) { t=index( ln, ", " ) } if ( t==0 ) { t=index( ln, ") " ) } if ( t > 0 ) { printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2) lcnt=0 } else { printf "%s", ln if ( ln0 < 78 ) { printf "\n" ; lcnt=0 } } } else { printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 ) if ( ln0 < 78 ) { printf "\n" ; lcnt=0 } } } } END { printf "\n/\n"} ' $* |sed '1,2d; /^$/ d; s/STORAGE *(INI/~ STORAGE (INI/g; s/, "/,~ "/g; s/ (\"/~ &/g; s/PCT[FI]/~ &/g; s/[( ]PARTITION /~&/g; s/) TABLESPACE/)~ TABLESPACE/g; s/ , / ,~/g; s/ DATAFILE /&~/' | tr "~" "\n" [ZFZHLHRDB1:oracle]:/tmp> [ZFZHLHRDB1:oracle]:/tmp>ksh /tmp/gettabddl.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql [ZFZHLHRDB1:oracle]:/tmp>more /tmp/gen_tabddl.sql ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" / CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS / CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING / ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" / ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE / ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE / ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO" / 这样运行起来就方便多了。 另外，使用imp工具的indexfile选项也可以把dmp文件中的表和索引的创建语句导出而不导入任何对象，命令如下： imp userid/userid@service_name file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n 示例如下所示： [oracle@rhel6lhr tmp]$ exp \'/ AS SYSDBA\' TABLES=SCOTT.EMP FILE=/tmp/exp_ddl_lhr_01.dmp LOG=/tmp/exp_table.log BUFFER=41943040 ROWS=N COMPRESS=N
Export: Release 11.2.0.3.0 - Production on Wed May 3 21:36:47 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP Export terminated successfully without warnings. [oracle@rhel6lhr tmp]$ imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n
Import: Release 11.2.0.3.0 - Production on Wed May 3 21:38:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set Import terminated successfully without warnings. [oracle@rhel6lhr tmp]$ more /tmp/get_ti_ddl.sql
REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME" REM VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, REM "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL REM DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ; CONNECT SCOTT; CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 REM STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ; REM ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ; [oracle@rhel6lhr tmp]$ 可以看到其中的创建表的SQL语句被注释掉了，这个可以用vi命令或者文本工具来处理，处理之后就可以直接使用了。 About Me ............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成 ● 最新修改时间：2018-04-01 06:00 ~ 2018-04-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群 小麦苗的微店 .............................................................................................................................................   |
【本文地址】